​​How to retrieve data from an external database in a CheckBoxList control and consuming it inside the form

Last Update: July 10, 2017

Here are the steps on how to do that:

  1. Open the form in design mode.
  2. Create a form that looks like the below image where SQLGetEmployeeInfo2 is a SQL Connector control:
    How to retrieve data from an external database in a CheckBoxList control and consuming it inside the form1.jpg
  3. Select the SQLGetEmployeeInfo2 then select the Control Settings property in the Control Properties pane. A Database Dialog Settings will appear. Set the Initial Connection Settings as follows:
    How to retrieve data from an external database in a CheckBoxList control and consuming it inside the form2.jpg
    Click Connect, then set the Database Content settings as follows (herein, we will connect to "Employees" table stored in MS SQL Server DB):
    How to retrieve data from an external database in a CheckBoxList control and consuming it inside the form3.jpg
    Please note that you can include the following:
    • A Where statement to filter the SQL query. For example, you can specify one of the following statements in the Where:
      • Where clause that based on static value. Example: Business_Unit_Name = ‘IT’.
      • Where clause that based on a dynamic value of another control in the form.
        ​Example: Business_Unit_Name = ‘#Control(TextBox9)’.
      • Where clause that include composite values. Example: Business_Unit_Name = ‘#Control(TextBox9)’ AND Business_Unit_Name LIKE ‘%Admin%’ AND Position LIKE ‘%Manager%’
    • When click on OK, the system saves the Database Dialog Settings and create a new system rule to execute the executeSQLQuery. Don’t delete this rule.
    • Check the option "Show All Data When Search Box is Empty". If checked, the data will be retrieved automatically when open the search dialog in the run-mode without the need to specify the value in the text-search criteria.
    • Ignore SQL Error: To ignore any SQL error that may fire at runtime.
  4. Select SQLGetEmployeeInfo2 control.
  5. In the Rules pane, click on the Add icon to add a new rule, the Rule Manager dialog will open.
    How to auto-shifting Panel control in a form2.jpg
  6. Add a new rule as follows:
    A. Change the Rule Name as desired.
    B. Change the Rule Type to Action.
    C. Change the Event Type to onChange. Check "Execute if event triggered only on this control".
    D. In the Action section, type the following script:
    generateSQLData(SQLGetEmployeeInfo2, ['Location_Name', 'Business_Unit_Name', 'Position'], ['txtLocation', 'txtDepartment', 'txtPosition'])
    E. Click on the Save button to save the rule.
    Note: You can use Assistance Panel to help you adding functions and related parameters, form variables and form controls to Conditions and/or Actions sections.
  7. Test the form by clicking on the Preview button in the View group.

Related Resources:
These resources may not reflect the same exact case steps.
https://www.youtube.com/embed/x_Ch55KnYOk